package mudbconnection;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import muobject.PhysicalSimpleSummary;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

@Component("DBPhysicalSimpleSummaryService")
public class DBPhysicalSimpleSummaryService implements DBPhysicalSimpleSummary{
	
	private JdbcTemplate jdbct;

	@Autowired
	public DBPhysicalSimpleSummaryService(DataSource jdbct) {
		super();
		this.jdbct = new JdbcTemplate(jdbct);
	}
	
	public Boolean ExistDataTable(){
		int tableCount = jdbct.queryForInt("Select count(*) from TempPhysicalSimpleSummary");
		return tableCount>0 ? true : false;
	}
	
	public List<PhysicalSimpleSummary> getAllPhysicalSimpleSummary(){
		@SuppressWarnings("unchecked")
		List<PhysicalSimpleSummary> physicalSimpleSummarys = (List<PhysicalSimpleSummary>)jdbct
				.query("Select * from TempPhysicalSimpleSummary",
				new PhysicalSimpleSummaryMapper());
		return physicalSimpleSummarys;
	}
	
	public boolean setPhysicalSimpleSummary (PhysicalSimpleSummary physicalSimpleSummary) {
		try{
			jdbct.update("insert into TempPhysicalSimpleSummary(year, grade, sclass, stno, classno, ename," +
					" cname, sex, age, testdate, hwscore, gripscore, jumpscore, situpscore, vcapacityscore, run69score, " +
					" run15score, totalscore, plevel) " +
					" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", 
				     new Object[] { physicalSimpleSummary.getYear(), physicalSimpleSummary.getGrade(),
					physicalSimpleSummary.getSclass(), physicalSimpleSummary.getStno(), physicalSimpleSummary.getClassno(),
					physicalSimpleSummary.getEname(), physicalSimpleSummary.getCname(), physicalSimpleSummary.getSex(),
					physicalSimpleSummary.getAge(), physicalSimpleSummary.getTestdate(), physicalSimpleSummary.getHwscorw(),
					physicalSimpleSummary.getGripscore(), physicalSimpleSummary.getJumpscore(), physicalSimpleSummary.getSitupscore(),
					physicalSimpleSummary.getVcapacityscore(), physicalSimpleSummary.getRun69score(), 
					physicalSimpleSummary.getRun15score(), physicalSimpleSummary.getTotalscore(), physicalSimpleSummary.getLevel() });
			return true;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	public int getPhysicalSexScoreCount(String sex, String item, int score){
		int count;
		try{
			if(score == 0){
				count = jdbct.queryForInt("select count(*) from TempPhysicalSimpleSummary where " +
						" Sex like ? " , new Object[]{ sex });
			}else{
				count = jdbct.queryForInt("select count(*) from TempPhysicalSimpleSummary where Sex like ? " +
					"and " + item + " = ? ", new Object[]{ sex, score});
			}
		}catch(Exception e){
			count=0;
		}
		return count;
	}
	
	public double getPhysicalSexScoreAvg(String sex, String item){
		double count;
		try{
			count = (Double) jdbct.queryForObject("select avg(" + item + ") from TempPhysicalSimpleSummary where " +
					" Sex like ? ", new Object[]{ sex }, Double.class);
		}catch(Exception e){
			count=0;
		}
		return count;
	}
	
	public boolean deleteAllPhysicalSimpleSummary() {
		try{
			jdbct.update("delete from TempPhysicalSimpleSummary");
			return true;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	class PhysicalSimpleSummaryMapper implements RowMapper{
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException{
			PhysicalSimpleSummary physicalSimpleSummary = null;
			try{
				physicalSimpleSummary = new PhysicalSimpleSummary(rs.getString("year"), rs.getString("grade"), rs.getString("sclass")
						, rs.getString("stno"), rs.getInt("classno"), rs.getString("ename"), rs.getString("cname")
						, rs.getString("sex"), rs.getInt("age"), rs.getString("testdate"), rs.getInt("hwscore")
						, rs.getInt("gripscore"), rs.getInt("jumpscore"), rs.getInt("situpscore"),rs.getInt("vcapacityscore")
						, rs.getInt("run69score"), rs.getInt("run15score"), rs.getDouble("totalscore"),rs.getInt("plevel"));
			}catch(Exception e){
				System.out.println(e.getMessage());
			}
			return physicalSimpleSummary;
		}
	}
}
